
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetReconcileUnMatched]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetReconcileUnMatched]
GO


/****** Object:  StoredProcedure [dbo].[GetReconcileUnMatched]    Script Date: 02/13/2007 14:32:52 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO


/************************************************************************************************/
/* Name		:GetReconcileUnMatched                                                                   */
/* Purpose	:Stored procedure for Reconciled Unmatched Report           											*/
/* PARAMETERS
* Name    			Description					
* -------------  	-------------------------------------------
* @domainUserID		Domain User Id of the logged User
* @orderTypeID 		Order Type
* @transactionStartDate Transaction Start Date
* @transactionEndDate	Transaction End Date

* ERRORS USED		Description
* -------------  	-------------------------------------------
* n/a
*
* RESULT SETs
---Insight
* Name    			Description					
* -------------  	----------------------------------------
* OrderTypeId		Order Type Identification Number
* OrderType		Order Type
* OrderNumber		Order Number
* ReqNumber		Requisition Number
* Vendor		Vendor Name
* TotalAmount		Total Amount


---PS
* OrderTypeId		Order Type Identification Number
* OrderType		Order Type
* OrderNumber		Order Number
* ReqNumber		Requisition Number
* Vendor		Vendor Name
* TotalAmount		Total Amount

* RETURN VALUE
* Value    			Description					
* -------------  	-----------------------------------------------
* n/a
*
******************************************************************/
CREATE  PROCEDURE [dbo].[GetReconcileUnMatched]
(
	@domainUserID			INTEGER,
	@orderTypeID			INTEGER,
	@transactionStartDate	DATETIME  = NULL,
	@transactionEndDate		DATETIME  = NULL,
	@orderNumber			VARCHAR(10) = NULL,
	@piID					INT = 0, -- 0 does not filter
	@chiefCodeList			VARCHAR(1000) = NULL,
	@fundNumber				VARCHAR(6)  = NULL,
	@amount					MONEY  = NULL,
	@requestorName			VARCHAR(50) = NULL,
	@vendorName				VARCHAR(64) = NULL,
	@invoiceNumber			VARCHAR(16) = NULL,
	@invoiceDate			DATETIME = NULL	
)
AS
BEGIN

    SET NOCOUNT  ON
	
	-- constants declarations
	DECLARE    @purchaseOrderType 	    INTEGER
	DECLARE    @travelVoucherOrderType 	INTEGER
	DECLARE    @chequeRequestOrderType 	INTEGER
	DECLARE    @journalEntryOrderType 	INTEGER
	DECLARE    @pettyCashOrderType 		INTEGER
   	
	--set constants
	SET	@purchaseOrderType  		= 1
	SET	@travelVoucherOrderType  	= 2
	SET	@chequeRequestOrderType  	= 3
	SET	@journalEntryOrderType  	= 4
	SET	@pettyCashOrderType  		= 5 
	 
	--only get agreement ids that the user has view people soft rights to...
	DECLARE @viewPeopleSoftBitValue INT 
	SET		@viewPeopleSoftBitValue = 512
	
	-- security data
	IF OBJECT_ID('tempdb..#accessibleFunds') IS NOT NULL 
		drop  table #accessibleFunds
	CREATE TABLE #accessibleFunds(FundId INTEGER, AccessLevel INTEGER) 
                
	DECLARE		@today DATETIME
	SET			@today = GETDATE()
	
	DECLARE	   @isExpensed 		BIT
	SET		   @isExpensed = 1
	
	-- inserting the security data to a temp table
	INSERT INTO	#accessibleFunds 
		exec FilterFunds @domainUserID, @viewPeopleSoftBitValue, @piID = @piID, @chiefCodeList = @chiefCodeList, @fundStatusTypeID = 1

	--Declare a temp table for inserting the Insight values
	 IF OBJECT_ID('tempdb..#insightAll') IS NOT NULL 
		drop  table #insightAll
	CREATE TABLE #insightAll(
				  OrderTypeId 		INTEGER
				, OrderType 		VARCHAR(50)
				, OrderID 			INTEGER
				, OrderNumber 		VARCHAR(10)
				, TotalAmount 		MONEY
				, RequestorName		VARCHAR(256)
				, InvoiceNumber 	VARCHAR(16)
				, InvoiceDate 		DATETIME
				, FundNumber 		VARCHAR(30)
				, Vendor 			VARCHAR(64)
				, TransactionDate 	DATETIME)

   IF @orderTypeID = @purchaseOrderType
	BEGIN
    
    -- Get all Unmatched reconciled records for Insight data - Type = POs	
	INSERT	INTO
		#insightAll
	SELECT 
		 uot.[Id] AS 'OrderTypeId'
		,uot.[Name]AS 'OrderType'
		,uoh.[Id] AS 'OrderID'
		,puoh.POId AS 'OrderNumber'
		,ISNULL(puoh.TotalAmount,0) AS 'TotalAmount'
		,uod.RequestorName	
		,NULL AS 'InvoiceNumber'
		,NULL AS 'InvoiceDate'
		,af.FundNumber AS 'FundNumber'
		,puoh.VendorName AS 'Vendor'
		,uoh.TransactionDate
	FROM 
		PurUserOrderHeader uoh
	INNER JOIN 
		PurPOUserOrderHeader puoh
	ON 
		uoh.[Id] = puoh.PurUserOrderHeaderId
	INNER JOIN 
		PurUserOrderType uot
	ON 
		uot.[Id] = uoh.PurUserOrderTypeId
	INNER JOIN 
		PurUserOrderDistribution uod
	ON 
		uoh.[Id] = uod.PurUserOrderHeaderId
	INNER JOIN 
		AgrAgreement_fct af
	ON 
		af.FundId = uod.AgrFundId
	
	INNER JOIN
  		#accessibleFunds acc
	ON
		acc.FundId = af.FundId 
		
	WHERE 
			(puoh.PurUserOrderHeaderId NOT IN (SELECT InsightId FROM PurReconciledOrder_Src WHERE OrderTypeId = @orderTypeID))	 
			AND 
	     	((@transactionStartDate IS NULL) OR  ((uoh.TransactionDate IS NOT NULL) AND  (uoh.TransactionDate >=  @transactionStartDate))) 
  			AND 
  	     	((@transactionEndDate IS NULL ) OR ((uoh.TransactionDate IS NOT NULL) AND (uoh.TransactionDate <=  @transactionEndDate)))
			AND	 
			(uot.[Id] = @orderTypeID)
			AND
			(@orderNumber IS NULL OR puoh.POId LIKE '%' + @orderNumber +'%')
			AND
			(@fundNumber IS  NULL OR af.FundNumber LIKE @fundNumber  + '%')
			AND
			(@amount IS NULL OR (puoh.TotalAmount between (@amount) - .5 AND (@amount + .5)))
			AND
			(@requestorName IS  NULL OR uod.RequestorName LIKE '%' + @requestorName + '%' )
			AND
			(@vendorName IS  NULL OR puoh.VendorName LIKE '%' + @vendorName +'%')
	GROUP BY 
		 uot.[Id]
		,uot.[Name]
		,uoh.[Id] 
		,puoh.POId
		,puoh.TotalAmount
		,uod.RequestorName
		,af.FundNumber
		,puoh.VendorName
		,uoh.TransactionDate
		
	   --Insight data - Type = POs
	   SELECT 
		 OrderTypeId
		,OrderType
		,OrderID
		,OrderNumber
		,RequestorName	
		,TotalAmount
		,Vendor
		,InvoiceNumber
		,InvoiceDate
		,FundNumber 
		,TransactionDate
	   FROM 
		#insightAll

        -- Get all Unmatched reconciled records for PS data - Type = POs	
        SELECT 
            rui.OrderTypeId,
            rui.OrderType,
		    rui.OrderID,
            psf.OrderNumber,
		    psf.RequestorName,
            CASE WHEN psf.ExpenseStatus = @isExpensed THEN
				SUM(ISNULL(psf.ExpensedToDate,0))-- includes freight
			ELSE 
				SUM(ISNULL(psf.OrderAmount,0))
			END AS 'TotalAmount'
            ,psf.VendorName AS 'Vendor'
		    ,NULL AS 'InvoiceNumber'
		    ,NULL AS 'InvoiceDate'
		    ,rui.FundNumber
		    ,psf.OrderDate AS 'TransactionDate'
        FROM #insightAll rui
		INNER JOIN PurPOAPSummary_Fct psf
		ON	psf.OrderNumber = rui.OrderNumber AND rui.OrderTypeId = psf.OrderTypeId
	   GROUP BY
		      rui.OrderTypeId,
	          rui.OrderType,
		      rui.OrderID,
	          psf.OrderNumber,
		      psf.RequestorName,
			  rui.RequestorName,
		      psf.ExpenseStatus,
	          psf.VendorName,
	          rui.FundNumber,
		      psf.OrderDate
	END

	ELSE IF @orderTypeID = @travelVoucherOrderType OR @orderTypeID = @chequeRequestOrderType
	 BEGIN
    -- Get all Unmatched reconciled records for Insight data - Type = Vouchers ie AP
	INSERT 	INTO
		#insightAll
	SELECT 
		 uot.[Id] AS 'OrderTypeId'
		,uot.[Name] AS 'OrderType'
		,uoh.[Id] AS 'OrderID'
		,vuoh.PSVoucherId AS 'OrderNumber'
		,ISNULL(vuoh.InvoiceAmount,0) AS 'TotalAmount'
		,uod.RequestorName
		,vuoh.InvoiceNumber
		,vuoh.InvoiceDate
		,af.FundNumber AS 'FundNumber'
		,vuoh.VendorName AS 'Vendor'
		,uoh.TransactionDate	
	FROM 
		PurUserOrderHeader uoh
	INNER JOIN
		PurVoucherUserOrderHeader vuoh
	ON
		uoh.[Id] = vuoh.PurUserOrderHeaderId
	INNER JOIN 
		PurUserOrderDistribution uod
	ON 
		uoh.[Id] = uod.PurUserOrderHeaderId
	INNER JOIN 
		PurUserOrderType uot
	ON 
		uot.[Id] = uoh.PurUserOrderTypeId
	INNER JOIN 
		AgrAgreement_fct af
	ON 
		af.FundId = uod.AgrFundId
	
	INNER JOIN
  		#accessibleFunds acc
	ON
		acc.FundId = af.FundId
	WHERE 
		(vuoh.PurUserOrderHeaderId NOT IN (SELECT InsightId FROM PurReconciledOrder_Src WHERE OrderTypeId = @orderTypeID))	
		AND 
	    ((@transactionStartDate IS NULL) OR  ((uoh.TransactionDate IS NOT NULL) AND  (uoh.TransactionDate >=  @transactionStartDate))) 
  		AND 
  	    ((@transactionEndDate IS NULL ) OR ((uoh.TransactionDate IS NOT NULL )AND (uoh.TransactionDate <=  @transactionEndDate)))
		AND    	
		(uot.[Id] = @orderTypeID)
		AND
		(@orderNumber IS NULL OR vuoh.PSVoucherId LIKE '%' + @orderNumber +'%')
		AND
		(@fundNumber IS  NULL OR af.FundNumber LIKE @fundNumber  + '%')
		AND
		(@amount IS NULL OR (vuoh.InvoiceAmount between (@amount) - .5 AND (@amount + .5)))
		AND
		(@requestorName IS  NULL OR uod.RequestorName LIKE '%' + @requestorName + '%' )
		AND
		(@vendorName IS  NULL OR vuoh.VendorName LIKE '%' + @vendorName +'%')
		AND
		(@invoiceNumber IS  NULL OR vuoh.InvoiceNumber LIKE '%' + @invoiceNumber +'%')
		AND
		(@invoiceDate IS  NULL OR vuoh.InvoiceDate = @invoiceDate)	
        GROUP BY
	 	 uot.[Id]
		,uot.[Name]
		,uoh.[Id] 
		,vuoh.PSVoucherId
		,vuoh.InvoiceAmount
		,uod.RequestorName
		,vuoh.InvoiceNumber
		,vuoh.InvoiceDate
		,af.FundNumber
		,vuoh.VendorName
		,uoh.TransactionDate
         
         
    -- Get all Unmatched reconciled records for Insight data - Type = Vouchers                  
	--Insight data - Type = APs
	    SELECT 
		 OrderTypeId
		,OrderType
		,OrderID
		,OrderNumber
		,RequestorName	
		,TotalAmount
		,Vendor
		,InvoiceNumber
		,InvoiceDate
		,FundNumber 
		,TransactionDate
	   FROM 
		#insightAll

       -- Get all Unmatched reconciled records for PS data - Type = APs	
       SELECT 
		rui.OrderTypeId,
		rui.OrderType,
		rui.OrderID,
		pf.OrderNumber,
		SUM(ISNULL(pf.MerchandiseAmount,0)) AS 'TotalAmount',
		pf.VendorName AS 'Vendor',
		pf.InvoiceDate ,
		pf.InvoiceNumber
		,pf.RequestorName
		,rui.FundNumber
		,rui.TransactionDate
       FROM 
         #insightAll rui
	   INNER JOIN
		 PurPOAPFilterAndDetails_Fct pf
	   ON pf.OrderNumber = rui.OrderNumber
	GROUP BY
		rui.OrderTypeId,
		rui.OrderType,
		rui.OrderID,
		pf.OrderNumber,
		pf.VendorName ,
		pf.InvoiceDate ,
		pf.InvoiceNumber
		,pf.RequestorName
		,rui.RequestorName
		,rui.FundNumber
		,rui.TransactionDate

	END
	ELSE IF @orderTypeID = @journalEntryOrderType OR @orderTypeID = @pettyCashOrderType 
	BEGIN
          -- Get all Unmatched reconciled records for Insight data - Type = Journals and PettyCash
        			
	 INSERT INTO
		#insightAll
	 SELECT 
		 uot.[Id] 	AS 'OrderTypeId'
		,uot.[Name] 	AS 'OrderType'
		,uoh.[Id] 	AS 'OrderID'
		,juoh.JournalId AS 'OrderNumber'
		,ISNULL(juoh.JournalAmount,0) AS 'TotalAmount'
		,uod.RequestorName
		,NULL 		AS 'InvoiceNumber'
		,NULL 		AS 'InvoiceDate'
		,af.FundNumber
		,NULL 		AS 'Vendor'
		,uoh.TransactionDate
	FROM
		PurUserOrderHeader uoh
	INNER JOIN
		PurJournalUserOrderHeader juoh
	ON
		uoh.[Id] = juoh.PurUserOrderHeaderId
	INNER JOIN
		PurUserOrderDistribution uod
	ON
		uoh.[Id] = uod.PurUserOrderHeaderId
	INNER JOIN 
		PurUserOrderType uot
	ON 
		uot.[Id] = uoh.PurUserOrderTypeId
	INNER JOIN 
		AgrAgreement_fct af
	ON 
		af.FundId = uod.AgrFundId
	
	INNER JOIN
  		#accessibleFunds acc
	ON
		acc.FundId = af.FundId 
	WHERE 
		(juoh.PurUserOrderHeaderId NOT IN (SELECT InsightId FROM PurReconciledOrder_Src WHERE OrderTypeId = @orderTypeID))
	AND 
	    ((@transactionStartDate IS NULL) OR  ((uoh.TransactionDate IS NOT NULL ) AND  (uoh.TransactionDate >=  @transactionStartDate))) 
  	AND 
  	    ((@transactionEndDate IS NULL ) OR ((uoh.TransactionDate IS NOT NULL) AND (uoh.TransactionDate <=  @transactionEndDate)))
	AND  
		(uot.[Id] = @orderTypeID)
	AND
		(@orderNumber IS NULL OR juoh.JournalId LIKE '%' + @orderNumber +'%')
	AND
		(@fundNumber IS  NULL OR af.FundNumber LIKE @fundNumber  + '%')
	AND
		(@amount IS NULL OR (juoh.JournalAmount between (@amount) - .5 AND (@amount + .5)))
	AND
		(@requestorName IS  NULL OR uod.RequestorName LIKE '%' + @requestorName + '%' )
	GROUP BY
		 uot.[Id] 
		,uot.[Name]
		,uoh.[Id]
		,juoh.JournalId
		,juoh.JournalAmount
		,uod.RequestorName
		,af.FundNumber
		,uoh.TransactionDate
		
	--Insight data - Type = JEs
	   SELECT 
		 OrderTypeId
		,OrderType
		,OrderID
		,OrderNumber
		,RequestorName
		,TotalAmount
		,Vendor
		,InvoiceNumber
		,InvoiceDate
		,FundNumber 
		,TransactionDate
	   FROM 
		#insightAll
		
          -- Get all Unmatched reconciled records for PS data - Type = Journals and PettyCash
		SELECT 
			DISTINCT
		      rui.OrderTypeId,
		      rui.OrderType,
		      rui.OrderID,
		      jts.JournalId AS 'OrderNumber',
			  (SELECT SUM(jts1.Amount) 
				FROM FinJournalTransaction_Src jts1 
				INNER JOIN FinAccount a1
				ON (jts1.AccountId = a1.Id and jts1.JournalId = jts.JournalId AND (LEFT(a1.Number,1)<'5' OR LEFT(a1.Number,1)>= '8'))
			  ) AS 'TotalAmount',
		      rui.FundNumber
		      ,NULL AS 'InvoiceNumber'
		      ,NULL AS 'InvoiceDate'
		      ,NULL AS 'RequestorName'
		      ,NULL AS 'Vendor'
		      ,jts.DateCreated AS 'TransactionDate'		
		FROM 
		      #insightAll rui
		INNER JOIN
			FinJournalTransaction_Src jts
		ON 	
			jts.JournalId = rui.OrderNumber AND jts.FundNumber = rui.FundNumber
		INNER JOIN
			FinAccount a
		ON
			jts.AccountId = a.Id
		WHERE
		(LEFT(a.Number,1)<'5' OR LEFT(a.Number,1)>= '8')
		GROUP BY
		      rui.OrderTypeId,
		      rui.OrderType,
		      rui.OrderID,
		      jts.JournalId,
		      jts.Amount,
		      rui.FundNumber,
		      jts.DateCreated
		END
END




GO

